using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

namespace Common.Database
{
    /// <summary>
    /// 
    /// </summary>
    public class TableIndexBuilder
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static TableColumnIndex[] GetTableIndexes(string connStr, string tblName)
        {
            Dictionary<string, TableColumnIndex> indexCollection = new Dictionary<string, TableColumnIndex>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select a.Name as IndexName, a.Type, a.Is_Unique, c.Name as ColumnName from " +
                                  "sys.indexes a, sys.index_columns b, sys.columns c where a.object_id=b.object_id and " +
                                  "a.object_id=c.object_id and b.column_id=c.column_id and a.object_id=" +
                                  "object_id('" + tblName + "')";
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    string indexName = reader["IndexName"].ToString();
                    int indexType = int.Parse(reader["Type"].ToString());
                    string colName = reader["ColumnName"].ToString();
                    bool unique = bool.Parse(reader["Is_Unique"].ToString());
                    if (indexCollection.ContainsKey(indexName.ToLower()))
                    {
                        TableColumnIndex index = indexCollection[indexName.ToLower()];
                        index.ColumnNames.Add(colName);
                    }
                    else
                    {
                        TableColumnIndex index = new TableColumnIndex();
                        index.IndexName = indexName;
                        index.Clustered = (indexType == 1 ? true : false);
                        index.IsUnique = unique;
                        index.ColumnNames = new List<string>();
                        index.ColumnNames.Add(colName);
                        index.TableName = tblName;
                        indexCollection.Add(indexName.ToLower(), index);
                    }
                }
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            if (indexCollection.Count > 0)
            {
                TableColumnIndex[] indexes = new TableColumnIndex[indexCollection.Count];
                indexCollection.Values.CopyTo(indexes, 0);
                return indexes;
            }
            else
                return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="maxRecordsChanged"></param>
        /// <returns></returns>
        public static bool NecessaryToRebuildIndex(string connStr, string tblName, int maxRecordsChanged)
        {
            int totalRecordsCount = (int) DbUtil.GetTableRecordCount(connStr, tblName);
            if (totalRecordsCount < maxRecordsChanged * 3)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="indexName"></param>
        public static void DropIndex(string connStr, string tblName, string indexName)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string dropSql = "IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'" +
                                 tblName + "') AND name = N'" + indexName + "')" +
                                 "DROP INDEX [" + indexName + "] ON " + tblName +
                                 " WITH ( ONLINE = OFF )";
                cmd.CommandText = dropSql;
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="index"></param>
        public static void BuildIndex(string connStr, TableColumnIndex index)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string createIndexSql = "create ";
                if(index.IsUnique)
                {
                    createIndexSql += "unique ";
                }
                if(index.Clustered)
                {
                    createIndexSql += "clustered ";
                }
                createIndexSql += "index [" + index.IndexName + "] on [" +
                                  index.TableName + "] (";
                for(int i=0;i<index.ColumnNames.Count;i++)
                {
                    createIndexSql += "[" + index.ColumnNames[i] + "]";
                    if(i<index.ColumnNames.Count-1)
                    {
                        createIndexSql += ",";
                    }
                }
                createIndexSql += ")";
                cmd.CommandText = createIndexSql;
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        public static void RebuildTableIndexes(string connStr, string tblName)
        {
            TableColumnIndex[] indexes = TableIndexBuilder.GetTableIndexes(connStr, tblName);
            if(indexes !=null && indexes.Length>0)
            {
                for(int i=0;i<indexes.Length;i++)
                {
                    TableIndexBuilder.DropIndex(connStr, indexes[i].TableName, indexes[i].IndexName);
                }

                for(int i=0;i<indexes.Length;i++)
                {
                    TableIndexBuilder.BuildIndex(connStr, indexes[i]);
                }
            }
        }
    }
}
